# -*- coding: utf-8 -*-
#
# The Philatelist program is a fork of the OpenNumismat program
# Copyright (c) 2011 - 2024 Vitaly Ignatov (OpenNumismat) <opennumismat@gmail.com>
# Copyright (c) 2021 - 2024 Victor Nabatov (Philatelist) <nabatov.victor@gmail.com>

import math
import os

from PySide6.QtCore import (
    Qt, QBuffer, QByteArray, QDate, QDateTime, QDir, QDirIterator, QFile, QFileInfo,
    QIODevice, QLocale, QModelIndex, QObject, QRectF, QT_TRANSLATE_NOOP, Signal as pyqtSignal
)
from PySide6.QtGui import QAction, QImage, QPainter
from PySide6.QtSql import QSqlDatabase, QSqlField, QSqlTableModel, QSqlQuery
from PySide6.QtWidgets import  QApplication, QDialog, QFileDialog, QMessageBox, QTableView

import Philatelist
from Philatelist import SLASH
from Philatelist.Collection.CollectionFields import (
    CollectionFields, ImagesFields, TraitsImagesFields, Statuses, FieldTypes as Type
)
from Philatelist.Collection.CollectionPages import CollectionPages
from Philatelist.Collection.Description import CollectionDescription
from Philatelist.Reference.Reference import CrossReferenceSection, Reference
from Philatelist.Reference.ReferenceDialog import AllReferenceDialog
from Philatelist.StampEditor.StampEditor import StampEditor
from Philatelist.Tools.CursorDecorators import waitCursorDecorator
from Philatelist.Tools.Gui import infoMessageBox, ProgressDialog, statusIcon
from Philatelist.Settings import Settings, BaseSettings
from Philatelist.Tools.Converters import numberWithFraction, htmlToPlainText
from Philatelist import version


class CollectionModel(QSqlTableModel):

    rowInserted = pyqtSignal(object)
    modelChanged = pyqtSignal()
    tagsChanged = pyqtSignal()
    IMAGE_FORMAT = 'jpg'
    SQLITE_READONLY = '8'

    def __init__(self, collection, parent=None):
        super().__init__(parent, collection.db)
        self.intFilter = ''
        self.extFilter = ''
        self.searchFilter = ''
        self.reference = collection.reference
        self.fields = collection.fields
        self.description = collection.description
        self.settings = collection.settings
        self.proxy = None
        self.rowsInserted.connect(self.rowsInsertedEvent)

    def supportedDropActions(self):
        return Qt.MoveAction

    def rowsInsertedEvent(self, parent, start, end):
        self.insertedRowIndex = self.index(end, 0)

    def data(self, index, role=Qt.DisplayRole):
        if role == Qt.DisplayRole:
            # Localize values
            data = super().data(index, role)
            field = self.fields.fields[index.column()]
            try:
                if field.name == 'status':
                    return Statuses[data]

                elif field.name == 'rating':
                    maxStarCount = self.settings['stars_count']
                    star_count = math.ceil(data.count('*') / (10 / maxStarCount))
                    # text = '★' * star_count    # black star
                    return '☆' * star_count      # white medium star

                elif field.type == Type.BigInt:
                    return QLocale.system().toString(int(data))

                elif field.type == Type.Text:
                    return htmlToPlainText(data)

                elif field.type == Type.Money:
                    text = QLocale.system().toString(float(data), 'f', precision=2)
                    dp = QLocale.system().decimalPoint()
                    return text.rstrip('0').rstrip(dp)

                elif field.type == Type.PreviewImage:
                    if data:
                        return self.getPreviewImage(data)

                    return None

                elif field.type == Type.Image:
                    if data:
                        query = QSqlQuery(self.database())
                        query.prepare("SELECT " + field + " FROM stamps WHERE id=?")
                        query.addBindValue(data)
                        query.exec_()
                        if query.first():
                            return query.record().value(0)

                    return None

                elif field.type == Type.Date:
                    date = QDate.fromString(data, Qt.ISODate)
                    return QLocale.system().toString(date, QLocale.ShortFormat)

                elif field.type == Type.DateTime:
                    date = QDateTime.fromString(data, Qt.ISODate)
                    # Timestamp in DB stored in UTC
                    date.setTimeSpec(Qt.UTC)
                    date = date.toLocalTime()
                    return QLocale.system().toString(date, QLocale.ShortFormat)

                return data

            except (ValueError, TypeError):
                return data

        elif role == Qt.UserRole:
            field = self.fields.fields[index.column()]
            if field.name == 'perforation_size':
                data = super().data(index, Qt.DisplayRole)
                data = numberWithFraction(data, self.settings['convert_fraction'])
                return data

            return super().data(index, Qt.DisplayRole)

        elif role == Qt.DecorationRole:
            field = self.fields.fields[index.column()]
            data = super().data(index, Qt.DisplayRole)
            if data:
                if field.name == 'status':
                    return statusIcon(data)
                else:
                    return self.reference.getIcon(field.name, data)

        elif role == Qt.TextAlignmentRole:
            field = self.fields.fields[index.column()]
            if field.type == Type.BigInt:
                return Qt.AlignRight | Qt.AlignVCenter

        return super().data(index, role)

    def dataDisplayRole(self, index):
        return super().data(index, Qt.DisplayRole)

    def addStamp(self, record, parent=None):
        record.setNull('id')
        if not record.value('status'):
            record.setValue('status', self.settings['default_status'])

        dialog = StampEditor(self, record, parent)
        result = dialog.exec_()
        if result == QDialog.Accepted:
            self.appendRecord(record)

        dialog.deleteLater()

    def addStamps(self, record, count, parent=None):
        record.setNull('id')
        if not record.value('status'):
            record.setValue('status', self.settings['default_status'])

        dialog = StampEditor(self, record, parent)
        if count > 1:
            dialog.setManyStamps()

        result = dialog.exec_()
        if result == QDialog.Accepted:
            self.appendRecord(record)

        button = dialog.clickedButton
        dialog.deleteLater()
        return button

    def appendRecord(self, record):
        rowCount = self.rowCount()
        tag_ids = record.value('tags')
        record.remove(record.indexOf('tags'))
        self.insertRecord(-1, record)
        self.submitAll()

        query = QSqlQuery(self.database())
        query.exec_('SELECT last_insert_rowid()')
        if query.first():
            stamp_id = query.value(0)
            for tag_id in tag_ids:
                id = self.getNewStampsTagId()
                query = QSqlQuery(self.database())
                query.prepare("INSERT INTO stamps_tags(id, stamp_id, tag_id) VALUES(?, ?, ?)")
                query.addBindValue(id)
                query.addBindValue(stamp_id)
                query.addBindValue(tag_id)
                query.exec_()

        if rowCount < self.rowCount():  # inserted row visible in current model
            if self.insertedRowIndex.isValid():
                self.rowInserted.emit(self.insertedRowIndex)

    def insertRecord(self, row, record):
        self._updateRecord(record)
        record.setNull('id')
        record.setValue('create_date', record.value('update_date'))
        query = QSqlQuery("SELECT MAX(sort_id) FROM stamps", self.database())
        query.first()
        sort_id = query.record().value(0)
#        if not sort_id:
#            sort_id = 0
#
#        record.setValue('sort_id', sort_id + 1)
        sort_id = 1 if not sort_id else sort_id + 1
        record.setValue('sort_id', sort_id)

        self.database().transaction()
        value = record.value('image')
        if value:
            query = QSqlQuery(self.database())
            query.prepare("INSERT INTO images (image) VALUES (?)")
            query.addBindValue(value)
            query.exec_()

            img_id = query.lastInsertId()
        else:
            img_id = None

        self.database().commit()

        record.setValue('image', img_id)
        record.remove(record.indexOf('image_id'))
        return super().insertRecord(row, record)

    def setRecord(self, row, record):
        self._updateRecord(record)
        self.database().transaction()
        # TODO : check that images was realy changed
        img_id = record.value('image_id')
        value = record.value('image')
        if not value:
            if img_id:
                query = QSqlQuery(self.database())
                query.prepare("DELETE FROM images WHERE id=?")
                query.addBindValue(img_id)
                query.exec_()

                img_id = None
        else:
            if img_id:
                query = QSqlQuery(self.database())
                query.prepare("UPDATE images SET image=? WHERE id=?")
                query.addBindValue(record.value('image'))
                query.addBindValue(img_id)
                query.exec_()
            else:
                query = QSqlQuery(self.database())
                query.prepare("INSERT INTO images (image) VALUES (?)")
                query.addBindValue(record.value('image'))
                query.exec_()

                img_id = query.lastInsertId()

        stamp_id = record.value('id')

        query = QSqlQuery(self.database())
        query.prepare("DELETE FROM stamps_tags WHERE stamp_id=?")
        query.addBindValue(stamp_id)
        query.exec_()

        for tag_id in record.value('tags'):
            id = self.getNewStampsTagId()
            query = QSqlQuery(self.database())
            query.prepare("INSERT INTO stamps_tags(id, stamp_id, tag_id) VALUES(?, ?, ?)")
            query.addBindValue(id)
            query.addBindValue(stamp_id)
            query.addBindValue(tag_id)
            query.exec_()

        record.remove(record.indexOf('tags'))
        self.database().commit()

        if img_id:
            record.setValue('image', img_id)

        record.remove(record.indexOf('image_id'))
        return super().setRecord(row, record)

    def setMultiRecord(self, multiRecord, usedFields, rows=None, parent=None):
        if not rows:
            rows = range(self.rowCount())

        progressDlg = ProgressDialog(
            self.tr("Updating records"),
            self.tr("Cancel"),
            len(rows),
            parent
        )
        new_tags = multiRecord.value('tags')
        for i in rows:
            progressDlg.step()
            if progressDlg.wasCanceled():
                break

            record = self.record(i)
            for j in range(multiRecord.count()):
                if usedFields[j] == Qt.Checked:
                    record.setValue(j, multiRecord.value(j))

            for field in ImagesFields:
                data = record.value(field)
                if data:
                    record.setValue(field, os.path.join(Philatelist.IMAGES_PATH, data))

            cur_tags = record.value('tags')
            for tag_id, state in new_tags.items():
                if state == Qt.Checked:
                    if tag_id not in cur_tags:
                        cur_tags.append(tag_id)

                elif state == Qt.Unchecked:
                    if tag_id in cur_tags:
                        cur_tags.remove(tag_id)

            record.setValue('tags', cur_tags)
            self.setRecord(i, record)

        progressDlg.setLabelText(self.tr("Saving..."))
        self.submitAll()
        progressDlg.reset()

    def record(self, row=-1):
        record = super().record(row) if row >= 0 else super().record()
        img_id = record.value('image')
        record.append(QSqlField('image_id'))
        # Preview image
        if img_id:
            record.setValue('image', self.getPreviewImage(img_id))
            record.setValue('image_id', img_id)
        else:
            record.setValue('image', None)

        tag_ids = []
        stamp_id = record.value('id')
        if stamp_id:
            query = QSqlQuery(self.database())
            query.prepare("SELECT tag_id FROM stamps_tags WHERE stamp_id=?")
            query.addBindValue(stamp_id)
            query.exec_()

            while query.next():
                tag_id = query.record().value(0)
                tag_ids.append(tag_id)

        record.append(QSqlField('tags'))
        record.setValue('tags', tag_ids)
        return record

    # Fill multi record for editing
    def multiRecord(self, rows=None):
        if not rows:
            rows = range(self.rowCount())

        multiRecord = self.record(rows[0])
        tags = {}
        for tag_id in multiRecord.value('tags'):
            tags[tag_id] = Qt.Checked

        usedFields = [Qt.Checked] * multiRecord.count()
        for i in rows[1:]:
            record = self.record(i)
            tags_diff = set(tags).symmetric_difference(record.value('tags'))
            for tag_id in tags_diff:
                tags[tag_id] = Qt.PartiallyChecked

            for j in range(multiRecord.count()):
                field = record.field(j)
                if field.name() == 'tags':
                    usedFields[j] = Qt.Unchecked
                else:
                    value = field.value()
                    if multiRecord.value(j) != value or not value:
                        multiRecord.setNull(j)
                        usedFields[j] = Qt.Unchecked

        multiRecord.setValue('tags', tags)
        return multiRecord, usedFields

    def removeRow(self, row):
        record = super().record(row)
        value = record.value('image')
        if value:
            query = QSqlQuery(self.database())
            query.prepare("DELETE FROM images WHERE id=?")
            query.addBindValue(value)
            query.exec_()

        stamp_id = record.value('id')
        if stamp_id:
            query = QSqlQuery(self.database())
            query.prepare("DELETE FROM stamps_tags WHERE stamp_id=?")
            query.addBindValue(stamp_id)
            query.exec_()

        return super().removeRow(row)

    def _updateRecord(self, record):
        if self.proxy:
            self.proxy.setDynamicSortFilter(False)

        # Creating preview image for list
        self._recalculateImage(record)
        record.setValue('perforation_size', numberWithFraction(record.value('perforation_size')))
        for field in ImagesFields:
            image_path = record.value(field)
            if image_path:
                record.setValue(field, image_path.replace(Philatelist.IMAGES_PATH + SLASH, ''))

        for field in TraitsImagesFields:
            image_path = record.value(field)
            if image_path:
                record.setValue(field, image_path.replace(Philatelist.RESOURCES_PATH + SLASH, ''))

        currentTime = QDateTime.currentDateTimeUtc()
        record.setValue('update_date', currentTime.toString(Qt.ISODateWithMs))

    def _recalculateImage(self, record):
        # Creating preview image for list
        if record.isNull('stamp_image') and record.isNull('reverse_image'):
            record.setNull('image')
        else:
            # Get height of list view for resizing images
            tmp = QTableView()
            height_multiplex = self.settings['image_height']
            height = int(tmp.verticalHeader().defaultSectionSize() * height_multiplex - 1)
            obverse_file = record.value('stamp_image')
            reverse_file = record.value('reverse_image')
            if obverse_file and not reverse_file:
                obverse_image, obverse_width = self.createScaledImage(obverse_file, height)
                preview_image = self.createPreviewImage(obverse_image, obverse_width, height)

            elif obverse_file and reverse_file:
                obverse_image, obverse_width = self.createScaledImage(obverse_file, height)
                reverse_image, reverse_width = self.createScaledImage(reverse_file, height)
                preview_image = self.createPreviewImage(obverse_image, obverse_width, height, reverse_image)

            record.setValue('image', preview_image)

    def moveRows(self, row1, row2):
        if self.proxy:
            self.proxy.setDynamicSortFilter(False)
            sort_column_id = self.fields.sort_id.id
            self.sort(sort_column_id, Qt.AscendingOrder)

        row_rang = []
        if row2 == -1:
            row_rang = range(row1 + 1, self.rowCount())

        elif row1 > row2:
            row_rang = range(row1 - 1, row2 - 1, -1)

        elif row1 < row2:
            row_rang = range(row1 + 1, row2 + 1)

        if row_rang:
            record = super().record(row1)
            old_sort_id = record.value('sort_id')
            for row in row_rang:
                record1 = super().record(row)
                sort_id = record1.value('sort_id')
                record1.setValue('sort_id', old_sort_id)
                super().setRecord(row, record1)
                old_sort_id = sort_id

            record.setValue('sort_id', old_sort_id)
            super().setRecord(row1, record)

        self.submitAll()

        if self.proxy:
            self.sort(-1, Qt.AscendingOrder)

    @waitCursorDecorator
    def setRowsPos(self, indexes):
        sorted_ids = sorted([index.data(Qt.UserRole) for index in indexes])
        for index, sort_id in zip(indexes, sorted_ids):
            record = super().record(index.row())
            record.setValue('sort_id', sort_id)
            super().setRecord(index.row(), record)

        self.submitAll()

    def recalculateAllImages(self, parent=None):
        while self.canFetchMore():
            self.fetchMore()

        rowCount = self.rowCount()
        if not parent:
            parent = self.parent()

        progressDlg = ProgressDialog(
            self.tr("Updating records"),
            self.tr("Cancel"),
            rowCount,
            parent
        )
        self.database().transaction()

        for row in range(rowCount):
            progressDlg.step()
            if progressDlg.wasCanceled():
                break

            record = self.record(row)
            self._recalculateImage(record)
            img_id = record.value('image_id')
            value = record.value('image')
            if value and img_id:
                query = QSqlQuery(self.database())
                query.prepare("UPDATE images SET image=? WHERE id=?")
                query.addBindValue(record.value('image'))
                query.addBindValue(img_id)
                query.exec_()

        progressDlg.setLabelText(self.tr("Saving..."))
        self.database().commit()
        progressDlg.reset()

    def submitAll(self):
        ret = super().submitAll()
        if not ret:
            if self.lastError().nativeErrorCode() == self.SQLITE_READONLY:
                message = self.tr("file is readonly")
            else:
                message = self.lastError().databaseText()

            QMessageBox.critical(
                self.parent(),
                self.tr("Saving"),
                self.tr("Can't save data: %s") % message
            )

        if self.proxy:
            self.proxy.setDynamicSortFilter(True)

        return ret

    def select(self):
        ret = super().select()
        self.modelChanged.emit()
        return ret

    def columnType(self, column):
        if isinstance(column, QModelIndex):
            column = column.column()

        return self.fields.fields[column].type

    def columnName(self, column):
        if isinstance(column, QModelIndex):
            column = column.column()

        return self.fields.fields[column].name

    def getPreviewImage(self, img_id):
        query = QSqlQuery(self.database())
        query.prepare("SELECT image FROM images WHERE id=?")
        query.addBindValue(img_id)
        query.exec_()
        if query.first():
            return query.record().value(0)

    def getImageTitle(self, img_id):
        query = QSqlQuery(self.database())
        query.prepare("SELECT title FROM stamps WHERE id=?")
        query.addBindValue(img_id)
        query.exec_()
        if query.first():
            return query.record().value(0)

    def clearFilters(self):
        self.intFilter = ''
        self.searchFilter = ''
        self.__applyFilter()

    def setFilter(self, filter_):
        self.intFilter = filter_
        self.__applyFilter()

    def setAdditionalFilter(self, filter_):
        self.extFilter = filter_
        self.__applyFilter()

    def setSearchFilter(self, filter_):
        self.searchFilter = filter_
        self.__applyFilter()

    def __applyFilter(self):
        filters = []
        if self.intFilter:
            filters.append(self.intFilter)

        if self.extFilter:
            filters.append(self.extFilter)

        if self.searchFilter:
            filters.append(self.searchFilter)

        combinedFilter = ' AND '.join(filters)

        # Checking for SQLITE_MAX_SQL_LENGTH (default value - 1 000 000)
        if len(combinedFilter) > 900000:
            QMessageBox.warning(
                self.parent(),
                self.tr("Filtering"),
                self.tr("Filter is too complex. Will be ignored")
            )
            return

        super().setFilter(combinedFilter)

    def isExist(self, record):
        fields = (
            'title', 'denomination', 'country', 'year', 'type', 'series', 'theme', 'status', 'material',
            'material_type', 'printing', 'perforation', 'perforation_size', 'variety', 'pay_date',
            'pay_price', 'seller', 'pay_place', 'sale_date', 'sale_price', 'buyer', 'sale_place'
        )
        filterParts = [field + '=?' for field in fields]
        sqlFilter = ' AND '.join(filterParts)

        query = QSqlQuery(self.database())
        query.prepare("SELECT 1 FROM stamps WHERE id<>? AND " + sqlFilter + " LIMIT 1")
        query.addBindValue(record.value('id'))
        for field in fields:
            query.addBindValue(record.value(field))

        query.exec_()
        if query.first():
            return True

        return False

    def getNewStampsTagId(self):
        id = 1
        sql = "SELECT MAX(id) FROM stamps_tags"
        query = QSqlQuery(sql, self.database())
        query.exec_()
        query.first()
        max_id = query.record().value(0)
        if max_id:
            id = max_id + 1

        return id

    def createScaledImage(self, file, height):
        image = QImage(file)
        image = image.scaledToHeight(height, Qt.SmoothTransformation)
        return image, image.width()

    def createPreviewImage(self, obverse_image, width, height, reverse_image=None):
        if not reverse_image:
            preview_image = QImage(width, height, QImage.Format_RGB32)
            paint = QPainter(preview_image)
            paint.drawImage(
                QRectF(0, 0, width, height),
                obverse_image,
                QRectF(0, 0, width, height)
            )
        else:
            preview_image = QImage(width * 2, height, QImage.Format_RGB32)
            paint = QPainter(preview_image)
            paint.drawImage(
                QRectF(0, 0, width, height),
                obverse_image,
                QRectF(0, 0, width, height)
            )
            paint.drawImage(
                QRectF(width, 0, width, height),
                reverse_image,
                QRectF(0, 0, width, height)
            )
        paint.end()

        ba = QByteArray()
        buffer = QBuffer(ba)
        buffer.open(QIODevice.WriteOnly)

        # Store as PNG for better view
        preview_image.save(buffer, 'png')
        return ba


class CollectionSettings(BaseSettings):

    Default = {
            'Type': version.AppName,
            'Version': 9,
            'ImageSideLen': 1024,
            'image_height': 1.5,
            'free_numeric': False,
            'convert_fraction': True,
            'images_at_bottom': False,
            'demo_status_used': False,
            'owned_status_used': True,
            'wish_status_used': True,
            'ordered_status_used': True,
            'sale_status_used': True,
            'sold_status_used': True,
            'exchange_status_used': True,
            'duplicate_status_used': True,
            'variety_status_used': True,
            'rarity_status_used': True,
            'demo_status_title': "Demo",
            'owned_status_title': "Owned",
            'wish_status_title': "Wish",
            'ordered_status_title': "Ordered",
            'sale_status_title': "Sale",
            'sold_status_title': "Sold",
            'exchange_status_title': "Exchange",
            'duplicate_status_title': "Duplicate",
            'variety_status_title': "Variety",
            'rarity_status_title': "Rarity",
            'rich_text': False,
            'default_status': 'owned',
            'colnect_category': '',
            'colnect_country': 0,
            'title_template': '<year> <denomination> <series>',
            'stamp_group_title': QT_TRANSLATE_NOOP("CollectionSettings", "Stamp"),
            'stamp_main_group_title': QT_TRANSLATE_NOOP("CollectionSettings", "Main details"),
            'stamp_state_group_title': QT_TRANSLATE_NOOP("CollectionSettings", "State"),
            'stamp_issue_group_title': QT_TRANSLATE_NOOP("CollectionSettings", "Issue"),
            'stamp_parameters_group_title': QT_TRANSLATE_NOOP("CollectionSettings", "Parameters"),
            'stamp_catalogue_group_title': QT_TRANSLATE_NOOP("CollectionSettings", "Catalogue"),
            'stamp_design_group_title': QT_TRANSLATE_NOOP("CollectionSettings", "Design"),
            'stamp_traits_group_title': QT_TRANSLATE_NOOP("CollectionSettings", "Traits"),
            'market_group_title': QT_TRANSLATE_NOOP("CollectionSettings", "Market"),
            'market_prices_group_title': QT_TRANSLATE_NOOP("CollectionSettings", "Prices"),
            'market_buy_group_title': QT_TRANSLATE_NOOP("CollectionSettings", "Buy"),
            'market_sale_group_title': QT_TRANSLATE_NOOP("CollectionSettings", "Sale"),
            'images_group_title': QT_TRANSLATE_NOOP("CollectionSettings", "Images"),
            'tags_group_title': QT_TRANSLATE_NOOP("CollectionSettings", "Tags"),
            'other_group_title': QT_TRANSLATE_NOOP("CollectionSettings", "Other"),
            'system_group_title': QT_TRANSLATE_NOOP("CollectionSettings", "System"),
            'relative_url': False,
            'stars_count': 10,
            'tags_used': True,
            'current_page': 0,
            'store': Philatelist.IMAGES_PATH
    }

    def __init__(self, db):
        super().__init__()
        self.db = db
        if 'settings' not in self.db.tables():
            self.create(self.db)

        query = QSqlQuery("SELECT * FROM settings", self.db)
        while query.next():
            record = query.record()
            title = record.value('title')
            if title in self.keys():
                default_value_type = type(self.Default[title])
                if default_value_type == int:
                    value = int(record.value('value'))

                elif default_value_type == float:
                    value = float(record.value('value'))

                elif default_value_type == bool:
                    value = record.value('value').lower() in ('true', '1')

                else:
                    value = record.value('value')

                self.__setitem__(title, value)

        for status, title in Statuses.items():
            # Fill default status titles
            self.Default[status + '_status_title'] = QApplication.translate("Status", title)

        # Fill global statuses from settings
        Statuses.init(self)
        for key in self.keys():
            if '_group_title' in key:
                self.Default[key] = QApplication.translate("CollectionSettings", self.Default[key])

    def keys(self):
        return self.Default.keys()

    def _getValue(self, key):
        return self.Default[key]

    def save(self):
        self.db.transaction()
        for key, value in self.items():
            query = QSqlQuery(self.db)
            query.prepare("INSERT OR REPLACE INTO settings (title, value) VALUES (?, ?)")
            query.addBindValue(key)
            query.addBindValue(str(value))
            query.exec_()

        self.db.commit()

    @staticmethod
    def create(db=QSqlDatabase()):
        db.transaction()
        sql = """CREATE TABLE settings (title CHAR NOT NULL UNIQUE, value CHAR)"""
        QSqlQuery(sql, db)
        for key, value in CollectionSettings.Default.items():
            query = QSqlQuery(db)
            query.prepare("INSERT INTO settings (title, value) VALUES (?, ?)")
            query.addBindValue(key)
            query.addBindValue(str(value))
            query.exec_()

        db.commit()


class Collection(QObject):

    def __init__(self, parent=None):
        super().__init__(parent)
        self.db = QSqlDatabase.addDatabase('QSQLITE')
        self._pages = None
        self.fileName = None

    def isOpen(self):
        return self.db.isValid() and self.fileName

    def open(self, fileName):
        self.fileName = None
        if QFileInfo(fileName).isFile():
            self.db.setDatabaseName(fileName)
            if not self.db.open() or not self.db.tables():
                print(self.db.lastError().text())
                QMessageBox.critical(
                    self.parent(),
                    self.tr("Open collection"),
                    self.tr("Can't open collection %s") % fileName
                )
                return False
        else:
            QMessageBox.critical(
                self.parent(),
                self.tr("Open collection"),
                self.tr("Collection %s not exists") % fileName
            )
            return False

        if ('stamps' not in self.db.tables()) or ('settings' not in self.db.tables()):
            QMessageBox.critical(
                self.parent(),
                self.tr("Open collection"),
                self.tr("Collection %s in wrong format") % fileName
            )
            return False

        self.settings = CollectionSettings(self.db)
        if self.settings['Type'] != version.AppName:
            QMessageBox.critical(
                self.parent(),
                self.tr("Open collection"),
                self.tr("Collection %s in wrong format") % fileName
            )
            return False

        if int(self.settings['Version']) > self.settings.Default['Version']:
            QMessageBox.critical(
                self.parent(),
                self.tr("Open collection"),
                self.tr(
                    "Collection %s a newer version.\n"
                    "Please update Philatelist.") % fileName
            )
            return False

        self.fields = CollectionFields(self.db)
        self.fileName = fileName
        self._pages = CollectionPages(self.db)
        self.description = CollectionDescription(self)
        self.__speedup()
        return True

    def create(self, fileName):
        self.fileName = None
        if QFileInfo(fileName).exists():
            QMessageBox.critical(
                self.parent(),
                self.tr("Create collection"),
                self.tr("Specified file already exists")
            )
            return False

        self.db.setDatabaseName(fileName)
        if not self.db.open():
            print(self.db.lastError().text())
            QMessageBox.critical(
                self.parent(),
                self.tr("Create collection"),
                self.tr("Can't open collection")
            )
            return False

        self.settings = CollectionSettings(self.db)
        self.fields = CollectionFields(self.db)
        self.createStampsTable()
        self.createTagsTable()
        self.fileName = fileName
        self._pages = CollectionPages(self.db)
        self.description = CollectionDescription(self)
        self.__speedup()
        return True

    def __speedup(self):
        settings = Settings()
        if settings['speedup'] == 1:
            QSqlQuery("PRAGMA synchronous=NORMAL", self.db)
            QSqlQuery("PRAGMA journal_mode=MEMORY", self.db)

    def createStampsTable(self):
        sqlFields = []
        for field in self.fields:
            if field.name == 'id':
                sqlFields.append('id INTEGER PRIMARY KEY')
            else:
                sqlFields.append("%s %s" % (field.name, Type.toSql(field.type)))

        sql = "CREATE TABLE stamps (" + ", ".join(sqlFields) + ")"
        QSqlQuery(sql, self.db)

        sql = "CREATE TABLE images (id INTEGER PRIMARY KEY, image BLOB)"
        QSqlQuery(sql, self.db)

    def createTagsTable(self):
        sql = """CREATE TABLE tags (
            id INTEGER NOT NULL PRIMARY KEY,
            tag TEXT,
            parent_id INTEGER,
            position INTEGER
        )"""
        QSqlQuery(sql, self.db)

        sql = """CREATE TABLE stamps_tags (id INTEGER PRIMARY KEY, stamp_id INTEGER, tag_id INTEGER)"""
        QSqlQuery(sql, self.db)

    def isReferenceAttached(self):
        return ('sections' in self.db.tables())

    def loadReference(self, fileName):
        if self.isReferenceAttached():
            self.reference = Reference(self.fields, self.parent(), db=self.db)
        else:
            self.reference = Reference(self.fields, self.parent())
            self.reference.open(fileName)

        self.reference.load()

    def getFileName(self):
        return QDir(self.fileName).absolutePath()

    def getCollectionName(self):
        return Collection.fileNameToCollectionName(self.fileName)

    def getDescription(self):
        return self.description

    def model(self):
        return self.createModel()

    def pages(self):
        return self._pages

    def createModel(self):
        model = CollectionModel(self)
        model.title = self.getCollectionName()
        model.setEditStrategy(QSqlTableModel.OnManualSubmit)
        model.setTable('stamps')
        model.select()
        for field in self.fields:
            model.setHeaderData(field.id, Qt.Horizontal, field.title)

        return model

    def fillReference(self):
        sections = self.reference.allSections()
        progressDlg = ProgressDialog(
            self.tr("Updating reference"),
            self.tr("Cancel"),
            len(sections),
            self.parent()
        )
        for columnName in sections:
            progressDlg.step()
            if progressDlg.wasCanceled():
                break

            refSection = self.reference.section(columnName)
            if isinstance(refSection, CrossReferenceSection):
                rel = refSection.model.relationModel(1)
                for i in range(rel.rowCount()):
                    data = rel.data(rel.index(i, rel.fieldIndex('value')))
                    parentId = rel.data(rel.index(i, rel.fieldIndex('id')))
                    query = QSqlQuery(self.db)
                    sql = "SELECT DISTINCT %s FROM stamps WHERE %s<>'' AND %s IS NOT NULL AND %s=?" % (columnName, columnName, columnName, refSection.parent_name)
                    query.prepare(sql)
                    query.addBindValue(data)
                    query.exec_()
                    refSection.fillFromQuery(parentId, query)
                    refSection.reload()
            else:
                sql = "SELECT DISTINCT %s FROM stamps WHERE %s<>'' AND %s IS NOT NULL" % (columnName, columnName, columnName)
                query = QSqlQuery(sql, self.db)
                refSection.fillFromQuery(query)
                refSection.reload()

        progressDlg.reset()

    def editReference(self):
        dialog = AllReferenceDialog(self.reference, self.parent())
        dialog.exec_()
        dialog.deleteLater()

    def attachReference(self):
        result = QMessageBox.information(
            self.parent(),
            self.tr("Attach"),
            self.tr("Attach current reference to a collection file?"),
            QMessageBox.Yes | QMessageBox.Cancel,
            QMessageBox.Cancel
        )
        if result == QMessageBox.Yes:
            progressDlg = ProgressDialog(
                self.tr("Attaching reference"),
                None,
                len(self.reference.sections),
                self.parent()
            )
            query = QSqlQuery(self.db)
            query.prepare("ATTACH ? AS ref")
            query.addBindValue(self.reference.fileName)
            res = query.exec_()
            if not res:
                progressDlg.reset()
                QMessageBox.critical(
                    self.parent(),
                    self.tr("Attaching reference"),
                    self.tr("Can't attach reference:\n%s" % query.lastError().text())
                )
                return

            reference = Reference(self.fields, self.parent(), db=self.db)
            reference.db.transaction()
            reference.create()

            for section in self.reference.sections:
                progressDlg.step()
                if res:
                    query = QSqlQuery(self.db)
                    query.prepare(
                        "INSERT INTO %s SELECT * FROM ref.%s" % (
                            section.table_name, section.table_name
                        )
                    )
                    res = query.exec_()

            if res:
                reference.db.commit()
                self.reference = reference
            else:
                reference.db.rollback()
                progressDlg.reset()
                QMessageBox.critical(
                    self.parent(),
                    self.tr("Attaching reference"),
                    self.tr("Can't attach reference:\n%s") % query.lastError().text()
                )

            QSqlQuery("DETACH ref", reference.db)
            self.reference.load()
            progressDlg.reset()
            self.__updateAttachAction()

    def detachReference(self):
        fileName, _selectedFilter = QFileDialog.getSaveFileName(
            self.parent(),
            self.tr("Save reference as"),
            filter=self.tr("Reference (*.ref)")
        )
        if fileName:
            if os.path.isfile(fileName):
                os.remove(fileName)

            progressDlg = ProgressDialog(
                self.tr("Detaching reference"),
                None,
                len(self.reference.sections),
                self.parent()
            )
            reference = Reference(self.fields, self.parent())
            if not reference.open(fileName, interactive=False):
                return

            query = QSqlQuery(reference.db)
            query.prepare("ATTACH ? AS ref")
            query.addBindValue(self.fileName)
            res = query.exec_()
            if not res:
                progressDlg.reset()
                QMessageBox.critical(
                    self.parent(),
                    self.tr("Detach reference"),
                    self.tr("Can't detach reference:\n%s") % query.lastError().text()
                )
                return

            reference.db.transaction()

            for section in self.reference.sections:
                progressDlg.step()
                if res:
                    query = QSqlQuery(reference.db)
                    query.prepare("INSERT INTO %s SELECT * FROM ref.%s" % (section.table_name, section.table_name))
                    res = query.exec_()

            if res:
                reference.db.commit()
            else:
                reference.db.rollback()
                progressDlg.reset()
                QMessageBox.critical(
                    self.parent(),
                    self.tr("Create reference"),
                    self.tr("Can't create reference:\n%s") % fileName
                )
                return

            for section in self.reference.sections:
                section.model.clear()

            self.db.transaction()

            for table_name in self.db.tables():
                if 'ref_' in table_name:
                    if res:
                        query = QSqlQuery(self.db)
                        query.prepare("DROP TABLE %s" % table_name)
                        res = query.exec_()

            if res:
                query = QSqlQuery(self.db)
                query.prepare("DROP TABLE sections")
                res = query.exec_()

            if res:
                query = QSqlQuery(self.db)
                query.prepare("DROP TABLE ref")
                res = query.exec_()

            if res:
                self.db.commit()
                self.reference = reference
            else:
                self.db.rollback()
                QMessageBox.critical(
                    self.parent(),
                    self.tr("Create reference"),
                    self.tr("Can't clear attached reference:\n%s") % query.lastError().text()
                )

            QSqlQuery("DETACH ref", reference.db)
            self.reference.load()
            progressDlg.reset()
            self.__updateAttachAction()

    def __updateAttachAction(self, is_connected=True):
        if is_connected:
            self.attachReferenceAct.triggered.disconnect()

        if self.isReferenceAttached():
            self.attachReferenceAct.setText(self.tr("Detach current reference"))
            self.attachReferenceAct.triggered.connect(self.detachReference)
        else:
            self.attachReferenceAct.setText(self.tr("Attach current reference"))
            self.attachReferenceAct.triggered.connect(self.attachReference)

    def referenceMenu(self, parent=None):
        fillReferenceAct = QAction(self.tr("Fill from collection"), parent)
        fillReferenceAct.triggered.connect(self.fillReference)

        editReferenceAct = QAction(self.tr("Edit..."), parent)
        editReferenceAct.triggered.connect(self.editReference)

        separator = QAction(parent)
        separator.setSeparator(True)

        self.attachReferenceAct = QAction(parent)
        self.__updateAttachAction(False)
        acts = (
            fillReferenceAct,
            editReferenceAct,
            separator,
            self.attachReferenceAct
        )
        return acts

    @waitCursorDecorator
    def __make_backup(self, backupFileName):
        srcFile = QFile(self.fileName)
        return srcFile.copy(backupFileName)

    def backup(self):
        backupDir = QDir(Settings()['backup'])
        if not backupDir.exists():
            backupDir.mkpath(backupDir.absolutePath())

        backupFileName = backupDir.filePath(
            "%s_%s.db" % (
                self.getCollectionName(),
                QDateTime.currentDateTime().toString('yyMMddhhmmss')
            )
        )
        if not self.__make_backup(backupFileName):
            QMessageBox.critical(
                self.parent(),
                self.tr("Backup collection"),
                self.tr("Can't make a collection backup at %s") % backupFileName
            )
            return False

        infoMessageBox(
            "backup",
            self.tr("Backup"),
            self.tr("Backup saved as %s") % backupFileName,
            parent=self.parent()
        )
        return True

    def isNeedBackup(self):
        settings = Settings()
        autobackup_depth = settings['autobackup_depth']
        filter_ = ('%s_????????????.db' % self.getCollectionName(),)
        files = QDirIterator(settings['backup'], filter_, QDir.Files)
        while files.hasNext():
            file_info = files.nextFileInfo()
            file_title = file_info.baseName()
            file_date = file_title[-12:]

            date_time = QDateTime.fromString(file_date, 'yyMMddhhmmss')
            if date_time.isValid():
                if date_time.date().year() < 2000:
                    date_time = date_time.addYears(100)

                date_time = date_time.toUTC()

                query = QSqlQuery(self.db)
                query.prepare("SELECT count(*) FROM stamps WHERE update_date > ?")
                query.addBindValue(date_time.toString(Qt.ISODate))
                query.exec_()
                query.first()
                if query.record().value(0) < autobackup_depth:
                    return False

        return True

    @waitCursorDecorator
    def vacuum(self):
        QSqlQuery("VACUUM", self.db)

    @staticmethod
    def fileNameToCollectionName(fileName):
        file = QFileInfo(fileName)
        return file.baseName()
